﻿using System;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;

namespace HiauPdm.Bll
{
    /// <summary>
    /// Excel操作类
    /// </summary>
    public class DExcel : IDisposable
    {
        private Excel.Application app;
        private Excel.Workbook workBook;
        private Excel.Worksheet workSheet;
        private Excel.Range range;
        private DateTime beforeTime;
        private DateTime afterTime;
        private object missing = Missing.Value;

        /// <summary>
        /// 构造函数
        /// </summary>
        public DExcel(string sExcelMbFile)
        {
            try
            {
                beforeTime = DateTime.Now;
                app = new Excel.ApplicationClass();
                app.Visible = false;
                app.DisplayAlerts = false;
                afterTime = DateTime.Now;
                workBook = app.Workbooks.Open(sExcelMbFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
            }
            catch { }
        }

        /// <summary>
        /// 写入DataTable数据
        /// </summary>
        public bool WriteDataTable(int iRowIndex, int iColIndex, DataTable dTable, params string[] sFields)
        {
            try
            {
                object[,] sArr = new object[dTable.Rows.Count, sFields.Length];
                for (int i = 0; i < dTable.Rows.Count; i++)
                {
                    for (int j = 0; j < sFields.Length; j++)
                    {
                        sArr[i, j] = dTable.Rows[i][sFields[j]];
                    }
                }
                range = (Excel.Range)workSheet.Cells[iRowIndex, iColIndex];
                range = range.get_Resize(dTable.Rows.Count, sFields.Length);
                range.Value = sArr;
                return true;
            }
            catch { this.Dispose(); return false; }
        }

        /// <summary>
        /// 写入单元格区域数据
        /// </summary>
        public bool WriteCellValues(int iRowIndex, int iColIndex, params object[] oValues)
        {
            try
            {
                object[,] sArr = new object[1, oValues.Length];
                for (int i = 0; i < oValues.Length; i++)
                {
                    sArr[0, i] = oValues[i];
                }
                range = (Excel.Range)workSheet.Cells[iRowIndex, iColIndex];
                range = range.get_Resize(1, oValues.Length);
                range.Value = sArr;
                return true;
            }
            catch { this.Dispose(); return false; }
        }

        /// <summary>
        /// 输出ExcelFile
        /// </summary>
        public bool OutputExcelFile(string sOutputFile)
        {
            try
            {
                workBook.SaveAs(sOutputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
                return true;
            }
            catch { return false; }
            finally { this.Dispose(); }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            try
            {
                if (workBook != null)
                {
                    workBook.Close(null, null, null);
                }
                if (app != null)
                {
                    app.Workbooks.Close();
                    app.Quit();
                }
                if (range != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                    range = null;
                }
                if (workSheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                    workSheet = null;
                }
                if (workBook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }
                if (app != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                    app = null;
                }
                GC.Collect();
                Process[] myProcesses = Process.GetProcessesByName("Excel");
                foreach (Process myProcess in myProcesses)
                {
                    if (myProcess.StartTime > beforeTime && myProcess.StartTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }
            catch { }
        }
    }
}
